package com.situ.company.employee.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


import com.situ.company.employee.dao.IEmployeeDao;
import com.situ.company.employee.model.EmployeeModel;
import com.situ.company.util.JDBCUtil;


public class EmployeeDaoImpl implements IEmployeeDao {
	
	String table = "employee";
	String cols  = "code,name,pass,code_dept,image";
	private Connection conn;
	private PreparedStatement ps;

	@Override
	public Integer insert(EmployeeModel model) {
		StringBuffer sql = new StringBuffer("insert into ");
		sql.append(table).append("(").append(cols).append(")").append("values(?,?,?,?,?)");
		
		try {
		conn = JDBCUtil.getConnection();
		ps = conn.prepareStatement(sql.toString());
		ps.setString(1, model.getCode());
		ps.setString(2, model.getName());
		ps.setString(3, model.getPass());
		ps.setString(4, model.getCodeDept());
		ps.setString(5, model.getImage());
		return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		
		return null;
	}

	@Override
	public Integer deleteByCode(EmployeeModel model) {
		StringBuffer sql = new StringBuffer("delete from ");
		sql.append(table).append(" where ").append(" code ").append(" = ").append("?");
		
		try {
		conn = JDBCUtil.getConnection();
		ps = conn.prepareStatement(sql.toString());
		ps.setString(1, model.getCode());
		return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		
		return null;
		
	}

	@Override
	public Integer delete(EmployeeModel model) {
		StringBuffer sql = new StringBuffer(" delete from ");
		sql.append(table);
		List<Object> values = appendWhere(sql,model);
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			for(int i=0;i<values.size();i++) {
				ps.setObject(i+1, values.get(i));
				return ps.executeUpdate();
			}
		} catch (Exception e) {
			
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

	

	@Override
	public Integer updateActive(EmployeeModel model) {
		
		StringBuffer sql = new StringBuffer(" update ");
		sql.append( table ).append( " set id=id ");
		List<Object> values = appendSet(sql, model);
		
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			for(int i=0;i<values.size();i++) 
			ps.setObject(i+1, values.get(i));
			//System.out.println(sql);	
			
			return ps.executeUpdate();
		} catch (Exception e) {
			
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

	private List<Object> appendSet(StringBuffer sql,EmployeeModel model){
		List<Object> values = new ArrayList<>();
		if(model.getName()!=null) {
			
			sql.append(" ,name=? ");
		values.add(model.getName());
		}
		if(model.getPass()!=null) {
			//MD5.encode(pass);
			sql.append(" ,pass=? ");
		values.add(model.getPass());
		}
		if(model.getCodeDept()!=null) {
			
			sql.append(" ,code_dept=? ");
		values.add(model.getCodeDept());
		}
        if(model.getImage()!=null){	
       
		sql.append(" ,image=? ");
		values.add(model.getImage());
		}
		sql.append(" where code=? ");
		
		values.add(model.getCode());			
		return values;
	}
	
	@Override
	public List<EmployeeModel> selectList(EmployeeModel model) {
		StringBuffer sql = new StringBuffer(" select id,");
		sql.append( " code,name,pass,code_dept,(select name from department where code = code_dept) name_dept,image " ).append( " from " ).append( table );
		List<Object> values = appendWhere(sql,model);
		ResultSet rs =null;
		List<EmployeeModel> result = new ArrayList<>();
		try {
			conn=JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString()); 
			for(int i=0;i<values.size();i++) 
				ps.setObject(i+1, values.get(i));
			
			rs =  ps.executeQuery();
			while(rs.next()) {//ORM映射
				EmployeeModel m1 = new EmployeeModel();
			  m1.setId(rs.getInt("id"));
			  m1.setCode(rs.getString("code"));
			  m1.setName(rs.getString("name"));
			  m1.setCodeDept(rs.getString("code_dept"));
			  m1.setNameDept(rs.getString("name_dept"));
			  result.add(m1);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return result;
	}

	@Override
	public EmployeeModel selectModel(EmployeeModel model) {
		StringBuffer sql = new StringBuffer("select id, ");
		sql.append(cols).append(" from ").append(table).append(" where code=? ") ;
		
		ResultSet rs =null;
		
		try {
			conn=JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString()); 
			ps.setString(1, model.getCode()); 
			
			rs =  ps.executeQuery();
			if(rs.next()) {
			  EmployeeModel m1 = new EmployeeModel();
			  m1.setId(rs.getInt("id"));
			  m1.setCode(rs.getString("code"));
			  m1.setName(rs.getString("name"));
			  m1.setPass(rs.getString("pass"));
			  m1.setCodeDept(rs.getString("code_dept"));
			  m1.setImage(rs.getString("image"));
			 
			  return m1;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

	@Override
	public Integer updateAll(EmployeeModel model) {
		StringBuffer sql = new StringBuffer(" update ");
		sql.append( table ).append(" set name=?,code_dept=? where code=? ");
		
		try {
		conn = JDBCUtil.getConnection();
		ps = conn.prepareStatement(sql.toString());
		ps.setString(1, model.getName());
		ps.setString(2, model.getCodeDept());
		ps.setString(3, model.getCode());
		return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		
		return null;
	}

	@Override
	public Integer update(EmployeeModel model) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String login(EmployeeModel model) {
		// TODO Auto-generated method stub
		return null;
	}
	
	public Integer selectCount(EmployeeModel model) {
		StringBuffer sql = new StringBuffer(" select count(1) from employee ");
		List<Object> vals = appendWhere(sql, model);
		return JDBCUtil.queInt(sql.toString(), vals);
		
	}
	
	private List<Object> appendWhere(StringBuffer sql, EmployeeModel model) {
		sql.append(" where 1=1 ");
		List<Object> values = new ArrayList<>();
		String code = model.getCode();
		if(code!=null&&!code.isEmpty()) {
			sql.append(" and code like ? ");
			values.add(code);
		}
		String name = model.getName();
		if(name!=null&&!name.isEmpty()) {
			sql.append(" and name like ? ");
			values.add(name);
		}
		String codeDept = model.getCodeDept();
		if(codeDept!=null&&!codeDept.isEmpty()) {
			sql.append(" and code_dept like ? ");
			values.add(codeDept);
	}
		if(model.isPageOn()) {
			sql.append(" limit ?,? ");
			values.add(model.getRowStart());
			values.add(model.getPageLimit());
		}
		return values;
	}

	

}


